Release 10.1A: OpenEdge Data Management:
SQL Reference

Unsubscripted array updates and inserts

OpenEdge SQL applies the reverse of the fetch algorithm for updates to unsubscripted Progress 4GL array columns. An SQL operation accepts an NVARCHAR string, complete with separators and any required escape characters. OpenEdge SQL converts the string to the underlying data type and stores it in the current element of the array in the OpenEdge database. SQL repeats this operation for each element of the array until all elements have been inserted.

This is the SQL syntax for an unsubscripted array update:

Syntax

UPDATE table_name SET array_name = ('char_element') WHERE where_criteria ;

Example

The ARRAYINT example assumes an integer array named ARRAYINT. The result of the UPDATE operation is:

ARRAYINT[ 1 ] = 13 ARRAYINT[ 2 ] = 15 ARRAYINT[ 3 ] = 19

UPDATE customer SET arrayint = '13;15;19'WHERE cust_num = 77 ;

If the number of elements in the NVARCHAR string does not match the number of elements in the target column for the update, OpenEdge SQL returns an error, unless there is exactly one element in the NVARCHAR string for an INSERT operation.

If there is a single element in the NVARCHAR string, you can use the OpenEdge SQL INSERT statement to propagate the value to all elements of the array.

This is the SQL syntax for this short form of an INSERT assignment:

Syntax

INSERT INTO table_name ( arr_col_name ) VALUES ( 'one_value' ) ;

Example

The following INSERT example illustrates how you can assign values to an entire date array from a single value in the VALUES clause of an SQL INSERT statement:

INSERT INTO customer ( begin_quota_date ) VALUES ( '01/01/03' ) ;

This INSERT example assigns the value '01/01/03' to every element of the date array 'begin_quota_date' column in the customer table.

Note: OpenEdge SQL does not support the assignment of a single value to an entire array using an SQL UPDATE statement.

Updating a single element of an array

To update a single element of an array, you must construct a string for the entire array, and assign the string to the array using an SQL UPDATE statement.

This is the SQL syntax for updating a single element of an array:

Syntax

UPDATE table_name SET array_col = literal_string ;

Examples

To assign a value to the first element of an array of size three, construct a literal string that concatenates these components:

Update value for the first element in the array.
Semicolon separator.
Values for elements two and three in the array.

Use PRO_ARR_ESCAPE to insert any necessary escape characters into the new value. Use PRO_ELEMENT to extract the values for elements two and three from the array. This example assigns the value 'aaa' to the first element of the arraychar array for customer 99, and retains the existing values for elements two and three:

UPDATE customer SET arraychar = PROARR_ESCAPE('aaa') || ';' || PRO_ELEMENT(arraychar,2,3) WHERE cust_num = 99 ;

To assign a value to the second element of an array of size three, construct a string that concatenates these components:

Value of the first element in the array.
Semicolon separator.
Update value for the second element.
Semicolon separator.
Value of the third element in the array.

Use PRO_ARR_ESCAPE to insert any necessary escape characters into the new value. Use PRO_ELEMENT to extract the first and third elements from the array. This example assigns the value 'bbb' to the second element of the arraychar array for customer 99, and retains the existing values for elements one and three in arraychar:

UPDATE customer SET arraychar = PRO_ELEMENT(arraychar,1,1) || ';' || PROARR_ESCAPE('bbb') || ';' || PRO_ELEMENT(arraychar,3,3) WHERE cust_num = 99 ;